package fy.hr.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import fy.config.FyConfig;
import fy.hr.bean.AwayChangeFromBean;
import fy.util.DBUtil;
import fy.util.MyUtil;

public class AwayTimeChangeFormDao {
	private static Log log = LogFactory.getLog(AwayTimeChangeFormDao.class);

	private static final String findInitialFormSQL = "select v.object_id, v.sender_id from ctp_affair v where v.id = ( select to_number( t.description ) from ctp_attachment t where t.sub_reference = ? and t.mime_type = 'collaboration') ";

	private static final String searchInitialFormInfoSQL = "select  b.awayaddress, b.awaybeginday, b.awayendday, b.awaynote from tbm_awayh h , tbm_awayb b where h.awayid = ? and h.pk_away_h = b.pk_away_h";

	private static final String updateAwayH = "update tbm_awayh h set h.awaytype = ?  where h.awayid = ?";

	private static final String updateAwayB = "update tbm_awayb b set b.awayaddress=?, b.awaybeginday = ?, b.awayendday = ? , b.Awaynote = ? where b.pk_away_h = ( select h.pk_away_h from tbm_awayh h where h.awayid = ? )";

	private AwayChangeFromBean acfb;

	public boolean findInitialFormNumber(boolean isNew) {

		boolean success = false;
		Connection conn = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		try {
			conn = DBUtil.connect(FyConfig.getOA_DB_CONFIG());
			log.info("isNew = "+isNew);
			// "select v.object_id, v.sender_id v.* from v3x_affair v where v.id
			// = ( select to_number( t.description ) from v3x_attachment t where
			// t.sub_reference = ? and t.mime_type = 'collaboration')";
			st = conn
					.prepareStatement(isNew ? "select a.id,a.start_member_id from col_summary a where a.subject = ?"
							: findInitialFormSQL);

			st.setString(1, acfb.getFormReference());
			log.info("acfb.getFormReference() = "+acfb.getFormReference());
			rs = st.executeQuery();

			if (rs.next()) {
				acfb.setObject_id(rs.getLong(1));
                acfb.setSender_id(rs.getLong(2));
				success = true;
			}

		} catch (Exception e) {
			log.error(
					new StringBuilder().append("查找单据编号失败, 表单名：").append(
							acfb.getBillname()), e);
			e.printStackTrace();
		} finally {
			DBUtil.close(rs);
			DBUtil.close(st);
			DBUtil.close(conn);
		}
		return success;
	}

	public boolean update() {

		boolean success = false;

		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rs = null;

		try {
			log.info("update---->>>>>yanch");
			conn = DBUtil.connect(FyConfig.getEHR_DB_CONFIG());
			// "select b.awayaddress, b.awaybeginday, b.awayendday, b.awaynote
			// from
			// tbm_awayh h , tbm_awayb b where h.awayid = ? and h.pk_away_h =
			// b.pk_away_h";

			pst = conn.prepareStatement(searchInitialFormInfoSQL);
			pst.setString(1, String.valueOf(acfb.getObject_id()));
			log.info("acfb.getObject_id() = "+acfb.getObject_id());
			rs = pst.executeQuery();

			if (rs.next()) {
				log.info("have next---->>>>>yanch");
				StringBuilder sb = new StringBuilder();
				sb.append("(变更No.").append(acfb.getNewAwayid()).append(")");

				sb.append(rs.getString(4)).append("（起讫时间：")
						.append(rs.getString(2)).append("至")
						.append(rs.getString(3)).append("）");

				DBUtil.close(rs);
				DBUtil.close(pst);
				conn.setAutoCommit(false);
				log.info("have next---->>>>>yanch"+acfb.getNewAwayid());
				log.info("have next---->>>>>yanch"+acfb.getOldAwayid());
				pst = conn.prepareStatement(updateAwayH);

				pst.setString(1, acfb.getAwaytype());
				pst.setString(2, acfb.getOldAwayid());

				pst.execute();

				DBUtil.close(pst);

				pst = conn.prepareStatement(updateAwayB);
				pst.setString(1, acfb.getAwayaddress());
				pst.setString(2, acfb.getAwaybeginday());
				pst.setString(3, acfb.getAwayendday());
				pst.setString(4,
						MyUtil.getLimitLengthString(sb.toString(), 500, "…"));
				pst.setString(5, String.valueOf(acfb.getObject_id()));

				pst.execute();
				conn.commit();
				success = true;
				sb = null;
			}

		} catch (Exception e) {
			DBUtil.rollback(conn);

			StringBuilder sb = new StringBuilder().append("表单《")
					.append(acfb.getBillname()).append("》数据写入HR数据库失败，单据编号为：")
					.append(this.acfb.getNewAwayid());

			log.error(sb, e);
			System.out.println(sb);
			e.printStackTrace();

			sb = null;

		} finally {
			DBUtil.close(rs);
			DBUtil.close(pst);
			DBUtil.setAutoCommit(conn, true);
			DBUtil.close(conn);
		}

		return success;
	}

	public void setChuChaiBianGengBean(AwayChangeFromBean acfb) {
		this.acfb = acfb;
	}
}
